<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
//echo $this->config->base_url();
require_once("PHPExcel.php");
class PHPExport {
	
	private static $m_pInstance; 
    private function __construct() {} 
	private $data;
	private $method;
	private $template;
	private $format;
	
    
	public static function getInstance() 
    { 
        if (!self::$m_pInstance) 
        { 
            self::$m_pInstance = new PHPExport(); 
        } 
        return self::$m_pInstance; 
    } 
    
    public function setData($data){
    	$this->data = $data;
    	//var_dump($this->data);
    }
    
	public function setTemplate($template){
    	$this->template = $template;
    }
    
	public function setFormat($format){
    	$this->format = $format;
    }
    
    public function send(){
    	switch($this->template) {
    		case 'payroll_journal':
    			$this->_payroll_journal();
    			break;
    		case 'payslip':
    			$this->_payslip();
    			break;
    			
    		case 'employee_timesheet':
    			$this->_employee_timesheet();
    			break;
    		
    	}
    }
    
private function _payslip(){
    	$objPHPExcel = new PHPExcel();
    	$data = $this->data;
    	$coverage = $data['period']['from'] .' - '. $data['period']['to'];
		$payroll_coverage = "Payroll for the Period covered: {$coverage}";
		$objPHPExcel->getProperties()->setCreator("{$data['info']['creator_name']}")
									 ->setLastModifiedBy("{$data['info']['current_user']}")
									 ->setTitle("{$data['info']['title']}")
									 ->setSubject("{$data['info']['subject']}")
									 ->setDescription("{$data['info']['description']}")
									 ->setKeywords("{$data['info']['keywords']}")
									 ->setCategory("{$data['info']['category']}");
		
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(3);
		// Add some data
		//$top_row = 0;
		$top_row = 0;
		$row = 0;
		
		$objPHPExcel->setActiveSheetIndex(0);
		
		$data_label = $data['label'];

		/*$objDrawing = new PHPExcel_Worksheet_Drawing();
		$objDrawing->setName('Logo');
		$objDrawing->setDescription('Logo');
		$objDrawing->setPath('images/time_republic_logo.jpg');
		$objDrawing->setHeight(27);
		$objDrawing->setCoordinates('A1');
		$objDrawing->setOffsetX(5);
		$objDrawing->setOffsetY(5);
		$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
		
		$objDrawing2 = new PHPExcel_Worksheet_Drawing();
		$objDrawing2->setName('Logo');
		$objDrawing2->setDescription('Logo');
		$objDrawing2->setPath('images/time_republic_logo.jpg');
		$objDrawing2->setHeight(27);
		$objDrawing2->setCoordinates('H1');
		$objDrawing2->setOffsetX(5);
		$objDrawing2->setOffsetY(5);
		$objDrawing2->setWorksheet($objPHPExcel->getActiveSheet());*/
		
		$styleThinBlackBorderOutline = array(
			'borders' => array(
				'outline' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN,
					'color' => array('argb' => 'FF000000'),
				),
			),
		);
		
		$styleTopBottomLineThick = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
			),
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				)
			),
			'font' => array(
				'bold' => true
			)
		);
		$styleTopBottomLineThin = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
			),
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			),
			'font' => array(
				'bold' => true
			)
		);
		
		
		
		$user_count = 0;
		$rowBase = 0;
		$inc = 0;
		foreach($data['payroll'] as $info){
			$user_count++;
			$row++;
			$columnLabel = ($user_count % 2 == 0)?'H':'A';
			
			$objDrawing = new PHPExcel_Worksheet_Drawing();
			$objDrawing->setName('Logo');
			$objDrawing->setDescription('Logo');
			$objDrawing->setPath('images/time_republic_logo.jpg');
			$objDrawing->setHeight(27);
			$objDrawing->setCoordinates($columnLabel . $row);
			$objDrawing->setOffsetX(5);
			$objDrawing->setOffsetY(5);
			$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
			
			//$row = 4 + (($user_count -1) * $rowBase) ;
			$row+=2;
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $payroll_coverage);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$row++;

			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "Name:");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			
			$columnLabel = ($user_count % 2 == 0)?'I':'B';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['first_name']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			
			$row++;
			
			$columnLabel = ($user_count % 2 == 0)?'H':'A';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "Client");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			
			$columnLabel = ($user_count % 2 == 0)?'I':'B';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "Goji Industry Philippines, Corp.");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			
			$row++;
			
			$columnLabel = ($user_count % 2 == 0)?'H':'A';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "GROSS PAY");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setBold(true);
			//$rowBase += $row;
			
			$row++;
			if($user_count % 2 == 0){
				$objPHPExcel->getActiveSheet()->setCellValue('K'.$row, "Hours");
				$objPHPExcel->getActiveSheet()->setCellValue('L'.$row, "Rate");
				$objPHPExcel->getActiveSheet()->setCellValue('M'.$row, "Amount");
				$objPHPExcel->getActiveSheet()->getStyle('K'.$row)->getFont()->setSize(10);
				$objPHPExcel->getActiveSheet()->getStyle('L'.$row)->getFont()->setSize(10);
				$objPHPExcel->getActiveSheet()->getStyle('M'.$row)->getFont()->setSize(10);
				$objPHPExcel->getActiveSheet()->getStyle('K'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				$objPHPExcel->getActiveSheet()->getStyle('L'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				$objPHPExcel->getActiveSheet()->getStyle('M'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			} else {
				$objPHPExcel->getActiveSheet()->setCellValue('D'.$row, "Hours");
				$objPHPExcel->getActiveSheet()->setCellValue('E'.$row, "Rate");
				$objPHPExcel->getActiveSheet()->setCellValue('F'.$row, "Amount");
				$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getFont()->setSize(10);
				$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getFont()->setSize(10);
				$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getFont()->setSize(10);
				$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			}
			$row++;
			$rowBase = $row;
			$row = $rowBase;
			for($i=0; $i < count($data['label']); $i++){
				
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $data['label'][$i]);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				$row++;
			}
			
			$columnLabel = ($user_count % 2 == 0)?'K':'D';
			$row = $rowBase;
			foreach($data['fields'] as $value){
				
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_' .$value]);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				$row++;
			}
				
			$columnLabel = ($user_count % 2 == 0)?'L':'E';
			$row = $rowBase;
			$additional = array('undertime'=>$info['user_rates']['regular_hours'],
			'late'=>$info['user_rates']['regular_hours']);
			$info['user_rates'] = array_merge($info['user_rates'], $additional);
			$gross = 0;
			foreach($info['user_rates'] as $key=>$value){
				$except = array('id','user_id');
				if(in_array($key, $except)) continue;
				//echo  $value;
				if($key == 'undertime') $value *= -1;
				if($value == null) $value = 0;
				$gross += $value;
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $value);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				$row++;
			}
			
			$columnLabel = ($user_count % 2 == 0)?'M':'F';
			$row = $rowBase;
			for($i=0; $i < count($info['user_rates']) - 2; $i++){
				if($user_count % 2 == 0){
					$valueStr = "=K{$row}*L{$row}";
				} else {
					$valueStr = "=D{$row}*E{$row}";
				}
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "{$valueStr}");
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				$row++;
			}
			
			// Deductions here...
			$columnLabel = ($user_count % 2 == 0)?'H':'A';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "Total Earnings");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			
			$columnLabel = ($user_count % 2 == 0)?'M':'F';

			$total_earnings ="=SUM({$columnLabel}8:{$columnLabel}28)";

			//$columnLabel = ($user_count % 2 == 0)?'H':'A';
			$gross_column = $columnLabel.$row;
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "{$total_earnings}");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray(
					array(
						'alignment' => array(
							'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
						),
						'borders' => array(
							'top'     => array(
			 					'style' => PHPExcel_Style_Border::BORDER_THICK
			 				),
			 				'bottom'     => array(
			 					'style' => PHPExcel_Style_Border::BORDER_THICK
			 				)
						),
						'font' => array(
							'bold' => true
						)
					)
			);
			$row++;
			
			$columnLabel = ($user_count % 2 == 0)?'H':'A';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "DEDUCTIONS");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setBold(true);
			
			$row++; 
			$contributions = array('sss_cont'=>"SSS Contributions",
			'philhealth_cont'=>'PhilHealth Contributions','hdmf_cont'=>'Pag-ibig Contributions',
			'sss_loan'=>'SSS Loans','hdmf_loan'=>'Pag-ibig Loans');
			$total_deductions = 0;
			foreach ($contributions as $key => $value){
				$amount = isset($info["{$key}"])?$info["{$key}"]:0;
				$total_deductions += $amount;
				$columnLabel = ($user_count % 2 == 0)?'H':'A';
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $value);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				$columnLabel = ($user_count % 2 == 0)?'M':'F';
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $amount);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				$row++;
			}

			$others = array('p_canteen'=>"Canteen",
			'p_uniform'=>'Others/Uniform','adjustment'=>'Adjustment',
			'insurance'=>'Insurance','total'=>'Total Deductions');
			
			$deductions_column = "";
			foreach ($others as $key => $value){
				$amount = isset($info["{$key}"])?$info["{$key}"]:0;
				$total_deductions += $amount;
				$columnLabel = ($user_count % 2 == 0)?'H':'A';
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $value);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				$columnLabel = ($user_count % 2 == 0)?'M':'F';
				if($key == 'total'){
					$amount = $total_deductions;
				}
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $amount);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				if($key == 'total'){
					$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleTopBottomLineThin);
				}
				$deductions_column = $columnLabel.$row;
				$row++;
			}
			
			// NET PAY
			$columnLabel = ($user_count % 2 == 0)?'H':'A';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "NET PAY");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setBold(true);
			$columnLabel = ($user_count % 2 == 0)?'M':'F';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=SUM($gross_column,-$deductions_column)");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setBold(true);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleTopBottomLineThick);
			$row+=2;	
			
			$columnLabel = ($user_count % 2 == 0)?'H':'A';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "Signature");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$columnLabel = ($user_count % 2 == 0)?'L':'E';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "Date");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			
			$top = intval(ceil($user_count / 2)) + ((ceil($user_count / 2) - 1) * 42);
			//if($user_count % 2 == 0) {
			//	$top = ($user_count / 2) * 43;
			//}else if($user_count == 1){
			//	$top = 1;
			//}
			
			$columnLabel = ($user_count % 2 == 0)?('H' .$top.':M'.$row):('A' .$top.':F'.$row);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel)->applyFromArray($styleThinBlackBorderOutline);
			
			if($user_count % 2 == 0) {
				$inc = ($user_count / 2) * 43;
			}
			$row = $top_row + $inc;
			
			
			
			
		}
			
		// Rename worksheet
		$objPHPExcel->getActiveSheet()->setTitle('Payslip');
		
		
		// Set active sheet index to the first sheet, so Excel opens this as the first sheet
		$objPHPExcel->setActiveSheetIndex(0);
		
		
		// Redirect output to a client’s web browser (Excel5)
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="payroll_payslip_' . date('Y-m-d').'.xls"');
		header('Cache-Control: max-age=0');
		
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;
    	
    }
    
    private function _payroll_journal(){
    	$objPHPExcel = new PHPExcel();
    	$data = $this->data;
    	$coverage = $data['period']['from'] .' - '. $data['period']['to'];
		$payroll_coverage = "Payroll for the Period covered: {$coverage}";
		$objPHPExcel->getProperties()->setCreator("{$data['info']['creator_name']}")
									 ->setLastModifiedBy("{$data['info']['current_user']}")
									 ->setTitle("{$data['info']['title']}")
									 ->setSubject("{$data['info']['subject']}")
									 ->setDescription("{$data['info']['description']}")
									 ->setKeywords("{$data['info']['keywords']}")
									 ->setCategory("{$data['info']['category']}");
									 
		// chr(65-90);
		
		//$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(3);
		// Add some data
		//$top_row = 3;
		//$row = $top_row;
		
		$objPHPExcel->setActiveSheetIndex(0);
		
		$data_label = $data['label'];
		
		$objDrawing = new PHPExcel_Worksheet_Drawing();
		$objDrawing->setName('Logo');
		$objDrawing->setDescription('Logo');
		$objDrawing->setPath('images/time_republic_logo.jpg');
		$objDrawing->setHeight(27);
		$objDrawing->setCoordinates('A1');
		$objDrawing->setOffsetX(5);
		$objDrawing->setOffsetY(5);
		$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
		
		$styleThinBlackBorderOutline = array(
			'borders' => array(
				'outline' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN,
					'color' => array('argb' => 'FF000000'),
				),
			),
		);
		
		$styleTopBottomLineThick = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
			),
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				)
			),
			'font' => array(
				'bold' => true
			)
		);
		$styleTopBottomLineThin = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
			),
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			),
			'font' => array(
				'bold' => true
			)
		);
		
		$styleCenterBold = array(
			'font' => array(
				'bold' => true
			),
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			)
		);
		
		$styleCenterNormal = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			)
		);
		
		$styleBorderCell = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			),
			'borders' => array(
				'right'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				
			)
		);
		
		$styleBorderThick = array(
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				),
 				'right'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				),
 				
			)
		);
		
		$styleBorderTopLeftCell = array(
			'borders' => array(
				'left'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				
			)
		);
		
		
		$user_count = 0;
		$rowBase = 0;
		$row = 4;
		$columnLabel = 'A';
		$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $payroll_coverage);
		$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
		$row++;

		$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "Goji Industry Philippines Corporation Assigned:");
		$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
		
		// Top Label
		$row = 7;
		$labelCount = 0;
		$strMin = 8;
		foreach ($data['label'] as $label) {
			$prefix = floor($labelCount / 26);
			$columnLabel = chr(65 + ($labelCount % 26));
			if($labelCount > 25){
				$columnLabel = chr(64 + $prefix).$columnLabel;
			}
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, strtoupper($label));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			$width = (strlen($label)) + 2;
			$width = $width < $strMin ? $strMin:$width;
			$objPHPExcel->getActiveSheet()->getColumnDimension($columnLabel)->setWidth($width);
			
			if($columnLabel == 'AO'){
			
				$objPHPExcel->getActiveSheet()->mergeCells("AO{$row}:BG{$row}");
				$objPHPExcel->getActiveSheet()->getStyle("AO{$row}:BG{$row}")->applyFromArray($styleBorderCell);
				$labelCount+=19;
			} else {
				$labelCount++;
			}
			
		}
		$objPHPExcel->getActiveSheet()->getStyle('A7:BI7')->applyFromArray($styleCenterBold);
		$row++;
		//$columnLabel = 'AO';
		$labelCount = 40; // 'O';
		foreach($data['deductions'] as $deduction){
			$prefix = floor($labelCount / 26);
			$columnLabel = chr(65 + ($labelCount % 26));
			if($labelCount > 25){
				$columnLabel = chr(64 + $prefix).$columnLabel;
			}
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, strtoupper($deduction));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			$width = (strlen($deduction)) + 2;
			$width = $width < $strMin ? $strMin:$width;
			$objPHPExcel->getActiveSheet()->getColumnDimension($columnLabel)->setWidth($width);
			if($columnLabel == 'AY'){
				$objPHPExcel->getActiveSheet()->mergeCells("AY{$row}:AZ{$row}");
				$objPHPExcel->getActiveSheet()->getStyle("AY{$row}:AZ{$row}")->applyFromArray($styleBorderCell);
				$labelCount+=2;
			} else {
				$labelCount++;
			}
		}
		$objPHPExcel->getActiveSheet()->getStyle('A8:BH8')->applyFromArray($styleCenterBold);
		
		$extra_label = array(
		'E'=>'38.125',
		'G'=>'47.656',
		'I'=>'3.813',
		'K'=>'49.56',
		'M'=>'64.43',
		'O'=>'4.956',
		'Q'=>'49.56',
		'S'=>'64.4310',
		'U'=>'4.956',
		'W'=>'61',
		'Y'=>'74.3488',
		'AA'=>'76.25',
		'AC'=>'99.125',
		'AE'=>'38.125',
		'AG'=>'99.125',
		'AI'=>'128.863',
		'AK'=>'7.625',
		'AY'=>'SSS',
		'AZ'=>'Pag-ibig',
		'BE'=>'C/A');
		
		$row = 9;
		$rate_row = $row;
		foreach ($extra_label as $key=>$label){
			$objPHPExcel->getActiveSheet()->setCellValue($key.$row, strtoupper($label));
			$objPHPExcel->getActiveSheet()->getStyle($key.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
		}
		$objPHPExcel->getActiveSheet()->getStyle("A$row:BI$row")->applyFromArray($styleCenterNormal);
		
		//$row = 11;
		$entryRow = 11;
		$row = $entryRow;
		//var_dump($data['payroll']);
		//die();
		foreach ($data['payroll'] as $info){
			
			$columnLabel = 'A';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, strtoupper($info['employee_id']));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'B';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, strtoupper($info['first_name']));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'C';
			$hours = $info['p_regular_hours'];
			$late_undertime = ($info['p_approved_undertime'] + $info['p_approved_late']);
			$actual_days = ($hours - $late_undertime) / 8;
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $actual_days);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'D';
			
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, ($hours - $late_undertime));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'E';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=D{$row} * E$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'F';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_regular_overtime']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'G';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=F{$row} * G$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'H';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_regular_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'I';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=H{$row} * I9");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'J';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_restday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'K';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=J{$row} * K$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'L';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_excess_restday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'M';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=L{$row} * M$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'N';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_restday_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'O';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=N{$row} * O$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'P';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_special_holiday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'Q';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=P{$row} * Q$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'R';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_excess_special_holiday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'S';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=R{$row} * S$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'T';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_special_holiday_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'U';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=T{$row} * U$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'V';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_special_holiday_restday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'W';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=V{$row} * W$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'X';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_excess_special_holiday_restday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'Y';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=X{$row} * Y$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'Z';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_legal_holiday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AA';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=Z{$row} * AA$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AB';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_excess_legal_holiday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AC';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=AB{$row} * AC$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AD';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_legal_holiday_unwork']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AE';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=AD{$row} * AE$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AF';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_legal_holiday_restday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AG';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=AF{$row} * AG$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AH';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_excess_legal_holiday_restday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AI';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=AH{$row} * AI$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AJ';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_legal_holiday_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AK';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=AJ{$row} * AK$rate_row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AL';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_adjustment']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AM';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['p_incentives']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = 'AN';
			$gross = "=E$row+G$row+I$row+K$row+M$row+O$row+Q$row+S$row+U$row+W$row+Y$row+AA$row+AC$row+AE$row+AG$row+AI$row+AK$row+AL$row+AM$row";
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $gross);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			/*$deduction_extra = array(
			'AO'=>'233.30',
			'AP'=>'494.70',
			'AQ'=>'10.00',
			'AR'=>'738.00',
			'AS'=>'75.00',
			'AT'=>'75.00',
			'AU'=>'150.00',
			'AV'=>'100.00',
			'AW'=>'100.00',
			'AX'=>'200.00');*/
			
			// Deduction iteration
			$labelCount = 40;
			$loop = true;
	    	while ($loop){
				$prefix = floor($labelCount / 26);
				$columnLabel = chr(65 + ($labelCount % 26));
				if($labelCount > 25){
					$columnLabel = chr(64 + $prefix).$columnLabel;
				}
				switch ($columnLabel){
					case 'AO':
						$value = isset($info['sss_cont'])?$info['sss_cont']:0;
						break;
					case 'AP':
						$value = isset($info['emp_sss_cont'])?$info['emp_sss_cont']:0;
						break;
					case 'AQ':
						$value = isset($info['ecc'])?$info['ecc']:10;
						break;
					case 'AR':
						$value = "=AO$row+AP$row";
						break;
					case 'AS':
						$value = isset($info['philhealth_cont'])?$info['philhealth_cont']:0;
						break;
					case 'AT':
						$value = isset($info['emp_philhealth_cont'])?$info['emp_philhealth_cont']:0;
						break;
					case 'AU':
						$value = "=AS$row+AT$row";
						break;
					case 'AV':
						$value = isset($info['hdmf_cont'])?$info['hdmf_cont']:0;
						break;
					case 'AW':
						$value = isset($info['emp_hdmf_cont'])?$info['emp_hdmf_cont']:0;
						break;
					case 'AX':
						$value = "=AV$row+AW$row";
						break;
					case 'AY':
						$value = isset($info['sss_loan'])?$info['sss_loan']:0;
						break;
					case 'AZ':
						$value = isset($info['hdmf_loan'])?$info['hdmf_loan']:0;
						break;
					case 'BA':
						$value = isset($info['p_canteen'])?$info['p_canteen']:0;
						break;
					case 'BB':
						$value = isset($info['p_canteen2'])?$info['p_canteen2']:0;
						break;
					case 'BC':
						$value = isset($info['pentax1'])?$info['pentax1']:0;
						break;
					case 'BD':
						$value = isset($info['pentax2'])?$info['pentax2']:0;
						break;
					case 'BE':
						$value = isset($info['adjustment'])?$info['adjustment']:0;
						break;
					case 'BF':
						$value = isset($info['p_uniform'])?$info['p_uniform']:0;
						break;
					case 'BG':
						$value = isset($info['p_other_deductions'])?$info['p_other_deductions']:0;
						break;
					case 'BH':
						//$value = "=AO$row+AS$row+AV$row+AY$row+AZ$row+BA$row+BB$row+BC$row+BD$row+BE$row+BF$row+BG$row";
						$value = "=SUM(AO$row:BG$row)";
						break;
				}
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $value);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
				if($columnLabel == 'BI'){
					$loop = false;
				}
				$labelCount++;
			}

			// NET
			$columnLabel = 'BI';
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=AN$row-BH$row");
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			//FORMAT_CURRENCY_USD_SIMPLE 
			$objPHPExcel->getActiveSheet()->getStyle("C$row:BI$row")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
			
			$row++;
		}
			
		$row+=5;
		
		// Total row
		$labelCount = 3;
		$loop = true;
    	while ($loop){
			$prefix = floor($labelCount / 26);
			$columnLabel = chr(65 + ($labelCount % 26));
			if($labelCount > 25){
				$columnLabel = chr(64 + $prefix).$columnLabel;
			}
			$total = "=SUM($columnLabel$entryRow:$columnLabel".($row-1).")";
			$objPHPExcel->getActiveSheet()->setCellValue( $columnLabel.$row, $total);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
			if($columnLabel == 'BI'){
				$loop = false;
			}
			$labelCount++;
		}
		$objPHPExcel->getActiveSheet()->getStyle("A$row:BI$row")->applyFromArray($styleBorderTopLeftCell);
		
		$objPHPExcel->getActiveSheet()->freezePane('F11');
		$objPHPExcel->getActiveSheet()->getStyle('A7:BI'.$row)->applyFromArray($styleBorderThick);
		$this->cellBorder(4, 59, 9, $styleBorderCell, $objPHPExcel);
		$objPHPExcel->getActiveSheet()->getStyle('E9:BI9')->applyFromArray($styleBorderTopLeftCell);
		
		// Rename worksheet
		$objPHPExcel->getActiveSheet()->setTitle('Payroll Journal');
		
		
		// Set active sheet index to the first sheet, so Excel opens this as the first sheet
		$objPHPExcel->setActiveSheetIndex(0);
		
		
		// Redirect output to a client’s web browser (Excel5)
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="payroll_journal_' . date('Y-m-d').'.xls"');
		header('Cache-Control: max-age=0');
		
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;
    	
    }
    
private function _employee_timesheet(){
    	$objPHPExcel = new PHPExcel();
    	$data = $this->data;
    	$coverage = $data['period']['from'] .' - '. $data['period']['to'];
		//$payroll_coverage = "Timesheet for the Period covered: {$coverage}";
		$objPHPExcel->getProperties()->setCreator("{$data['info']['creator_name']}")
									 ->setLastModifiedBy("{$data['info']['current_user']}")
									 ->setTitle("{$data['info']['title']}")
									 ->setSubject("{$data['info']['subject']}")
									 ->setDescription("{$data['info']['description']}")
									 ->setKeywords("{$data['info']['keywords']}")
									 ->setCategory("{$data['info']['category']}");
									 
		// chr(65-90);
		
		//$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(3);
		// Add some data
		//$top_row = 3;
		//$row = $top_row;
		
		$objPHPExcel->setActiveSheetIndex(0);
		
		$data_label = $data['label'];
		
		$objDrawing = new PHPExcel_Worksheet_Drawing();
		$objDrawing->setName('Logo');
		$objDrawing->setDescription('Logo');
		$objDrawing->setPath('images/time_republic_logo.jpg');
		$objDrawing->setHeight(27);
		$objDrawing->setCoordinates('A1');
		$objDrawing->setOffsetX(5);
		$objDrawing->setOffsetY(5);
		$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
		
		$styleThinBlackBorderOutline = array(
			'borders' => array(
				'outline' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN,
					'color' => array('argb' => 'FF000000'),
				),
			),
		);
		
		$styleTopBottomLineThick = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
			),
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				)
			),
			'font' => array(
				'bold' => true
			)
		);
		$styleTopBottomLineThin = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
			),
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			),
			'font' => array(
				'bold' => true
			)
		);
		
		$styleCenterBold = array(
			'font' => array(
				'bold' => true
			),
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			)
		);
		
		$styleCenterNormal = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			)
		);
		
		$styleBorderCell = array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			),
			'borders' => array(
				'right'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				
			)
		);
		
		$styleBorderThick = array(
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				),
 				'right'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				),
 				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				),
 				
			)
		);
		
		$styleBorderTopThick = array(
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THICK
 				)
 				
			)
		);
		
		$styleBorderTopLeftCell = array(
			'borders' => array(
				'left'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
 				
			)
		);
		
		$styleTopLineThin = array(

			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			)
		);
		
		$user_count = 0;
		$rowBase = 0;
		$row = 4;
		$columnLabel = 'A';
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, "Period:");
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $coverage);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setSize(10);
		$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getFont()->setSize(10);
		$row++;

		//$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, );
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, "Client:");
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$row, "Goji Industry Philippines Corporation");
		
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setSize(10);
		$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getFont()->setSize(10);
		
		$row++;
		
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, "Employee:");
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $data['employee_info']->first_name.' '.$data['employee_info']->last_name. " [{$data['employee_info']->employee_id}]");
		
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setSize(10);
		$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getFont()->setSize(10);
		
		// Top Label
		$row = 8;
		$labelCount = 0;
		$strMin = ($row + 1);
		$orig_row = $row;
		foreach ($data['label'] as $label) {
			$prefix = floor($labelCount / 26);
			$columnLabel = chr(65 + ($labelCount % 26));
			if($labelCount > 25){
				$columnLabel = chr(64 + $prefix).$columnLabel;
			}
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, strtoupper($label));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			$width = (strlen($label)) + 2;
			$width = $width < $strMin ? $strMin:$width;
			$objPHPExcel->getActiveSheet()->getColumnDimension($columnLabel)->setWidth($width);
			
			$labelCount++;
			
		}
		$objPHPExcel->getActiveSheet()->getStyle("A$orig_row:V$orig_row")->applyFromArray($styleCenterBold);

		
		//$row = 11;
		$entryRow = $orig_row + 2;
		$row = $entryRow;
		
		$labelCount = 0;
		foreach ($data['payroll'] as $info){
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, strtoupper($info['date']));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, strtoupper($info['time_in']));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['time_out']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['regular_hours']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $this->number_format($info['approved_undertime']));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $this->number_format($info['approved_overtime']));
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['approved_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['rest_day']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['excess_rest_day']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['rest_day_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['special_holiday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['excess_special_holiday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['special_holiday_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['special_holiday_rest_day']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['excess_special_holiday_rest_day']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['special_holiday_rest_day_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['legal_holiday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['excess_legal_holiday']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['legal_holiday_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['legal_holiday_rest_day']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['excess_legal_holiday_rest_day']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['legal_holiday_rest_day_night_differential']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			$columnLabel = $this->nextColumn($labelCount++);
			$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $info['legal_holiday_unwork']);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			

			// NET
			//$columnLabel = 'BG';
			//$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, "=AN$row-BF$row");
			//$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			//$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			
			//FORMAT_CURRENCY_USD_SIMPLE 
			//$objPHPExcel->getActiveSheet()->getStyle("C$row:BG$row")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
			$labelCount = 0;
			$row++;
		}
			
		//$row++;
		
		// Total row
		
		if(!empty($data['payroll'])) {
			for($i=0; $i<20; $i++) {
				$columnLabel = chr(68 + $i);//'D';
				$total_reg_hours ="=SUM({$columnLabel}9:{$columnLabel}".($row-1).")";
				$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, $total_reg_hours);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
				$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($styleBorderCell);
			}
			$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->applyFromArray($styleBorderTopLeftCell);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$orig_row .':'.$columnLabel.$orig_row)->applyFromArray($styleTopLineThin);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$entryRow .':'.$columnLabel.$entryRow)->applyFromArray($styleTopLineThin);
			//$objPHPExcel->getActiveSheet()->getStyle("D{$row}:W{$row}")->applyFromArray($styleBorderTopThick);
			
		
		
		}

		
		$objPHPExcel->getActiveSheet()->setTitle('Employee Timesheet');
		
		
		// Set active sheet index to the first sheet, so Excel opens this as the first sheet
		$objPHPExcel->setActiveSheetIndex(0);
		
		
		// Redirect output to a client’s web browser (Excel5)
		$filename = '';
		if(isset($data['employee_info']->first_name)) {
			$filename .= strtoupper($data['employee_info']->first_name);
		}
		if(isset($data['employee_info']->last_name) && trim($data['employee_info']->last_name) != '') {
			$filename .= '_'. strtoupper($data['employee_info']->last_name);
		}
		if(isset($data['employee_info']->employee_id)) {
			$filename .= '_'. strtoupper($data['employee_info']->employee_id);
		}
		
		$filename .= '_TIMESHEET_'.date('Y-m-d').'.xls';
		
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="'.$filename.'"');
		header('Cache-Control: max-age=0');
		
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;
    	
    }
    
    
    
    
    
    function cellBorder($labelCount, $end, $row, $style, &$objPHPExcel){
	   // $labelCount = 40; // 'O';
		for($i=$labelCount; $i<$end; $i++){
			$prefix = floor($i / 26);
			$columnLabel = chr(65 + ($i % 26));
			if($i > 25){
				$columnLabel = chr(64 + $prefix).$columnLabel;
			}
			//$objPHPExcel->getActiveSheet()->setCellValue($columnLabel.$row, strtoupper($deduction));
			//$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->getFont()->setSize(10);
			$objPHPExcel->getActiveSheet()->getStyle($columnLabel.$row)->applyFromArray($style);
			//$labelCount++;
		}
    }

    private function nextColumn($labelCount){
    	$prefix = floor($labelCount / 26);
		$columnLabel = chr(65 + ($labelCount % 26));
		if($labelCount > 25){
			$columnLabel = chr(64 + $prefix).$columnLabel;
		}
		return $columnLabel;
    }
    
    private function number_format($number, $digit = 3){
    	return number_format($number, $digit, '.', '');
    }
}